﻿using System;
using System.Collections;
using System.Data;
using NLog;
using System.Windows.Forms;

namespace Tab2Sql
{


    /// <summary>
    /// Pivot class. This is a misnamed feature as actually this is more accurately a cross-tab feature. Maybe.
    /// Anyway, the theory is that user inputs data that looks something like the below 
    /// (list of most popular boys names in UK for various years) :
    /// 
    ///    RNK	1904	 1914	    1924	  1934	     1944
	///    ===  ====     ====       ====      ====       ====
	///    1	WILLIAM	 JOHN	    JOHN	  JOHN	     JOHN
	///    2	JOHN	 WILLIAM	WILLIAM	  PETER	     DAVID
	///    3	GEORGE	 GEORGE	    GEORGE	  WILLIAM	 MICHAEL
	///    4	THOMAS	 THOMAS	    JAMES	  BRIAN	     PETER
	///    5	ARTHUR	 JAMES	    THOMAS	  DAVID	     ROBERT
	///    6	JAMES	 ARTHUR	    RONALD	  JAMES	     ANTHONY
	///    7	CHARLES	 FRED	    KENNETH	  MICHAEL	 BRIAN
	///    8	FRED	 ALBERT	    ROBERT	  RONALD	 ALAN
	///    9	ALBERT	 CHARLES	ARTHUR	  KENNETH	 WILLIAM
	///    10	ERNEST	 ROBERT	    FRED	  GEORGE	 JAMES
	/// 
	/// The output is (truncated):
	/// 
	///  UID	RANK	QNumber	Q	Answer	
	///		1	1	0	RANK	1	
	///		2	1	1	1904	WILLIAM	
	///		3	1	2	1914	JOHN	
	///		4	1	3	1924	JOHN	
	///		5	1	4	1934	JOHN	
	///		6	1	5	1944	JOHN	
	///		7	1	6	1954	DAVID	
	///		8	1	7	1964	DAVID	
	///		9	1	8	1974	PAUL	
	///		10	1	9	1984	CHRISTOPHER	
	///		11	1	10	1994	THOMAS	
	///		12	2	0	RANK	2	
	///		13	2	1	1904	JOHN	
	///		14	2	2	1914	WILLIAM	
	///		15	2	3	1924	WILLIAM	
	///		16	2	4	1934	PETER	
	///		17	2	5	1944	DAVID	
	///		18	2	6	1954	JOHN	
	///		19	2	7	1964	PAUL	
	///		20	2	8	1974	MARK	
	///		21	2	9	1984	JAMES	
	///		22	2	10	1994	JAMES	
	///     etc...
	
    /// </summary>
    internal class Pivot
    {
        private static Logger logger = LogManager.GetCurrentClassLogger ();
		private DataTable endDT;
        private DataTable startDT;
		
		
		public Pivot(DataTable dt)
        {
            startDT = dt;
        }

        public DataTable EndDT
        {
            get { return endDT; }
        }

        public void DoPivot ()
        {
        	var dt = new DataTable ();
        	var keyColumn = new DataColumn ("UID");
        	var firstColumn = new DataColumn (startDT.Columns[0].ColumnName);
        	var secondColumn = new DataColumn ("QNumber");

            var headerColumn = new DataColumn ("Q");
        	var valueColumn = new DataColumn ("Answer");
        	dt.Columns.Add (keyColumn);
        	dt.Columns.Add (firstColumn);
        	dt.Columns.Add (secondColumn);
        	dt.Columns.Add (headerColumn);
        	dt.Columns.Add (valueColumn);

            var colValues = new ArrayList ();
        	var colNumbers = new ArrayList ();
        	int iColumnCounter = 0;
        	foreach (DataColumn dc in startDT.Columns)
            {
        		string tmpColName = dc.ColumnName;
        		int tmpColNumber = dc.Ordinal;
        		colValues.Add (tmpColName);
        		colNumbers.Add (tmpColNumber);
        		iColumnCounter++;
        	}
        	int i = 0;
        	iColumnCounter = 0;
        	var rmv = new char[3];
        	rmv[0] = '[';
        	rmv[1] = ']';
        	rmv[2] = ' ';
        	try
            {
        		foreach (DataRow dr in startDT.Rows)
                {

                    iColumnCounter = 0;
        			foreach (string colValue in colValues)
                    {
        				i++;

                        DataRow drn = dt.NewRow ();
        				drn[0] = i;
        				drn[1] = dr.Field<string> (0).Trim ();
        				drn[2] = colNumbers[iColumnCounter];
        				drn[3] = colValue.Trim (rmv);
        				drn[4] = dr[colValue];
        				
                        iColumnCounter++;
        				dt.Rows.Add (drn);
        			}
        		}
        		dt.AcceptChanges ();
        		endDT = dt;
        	
            }
            catch (Exception ex)
            {
        		logger.Error (ex.Message);
				MessageBox.Show(ex.Message);
            }
            dt.Dispose();
        }

        /// <summary>
        /// alt.pivot
        /// </summary>
        /// <param name="dataValues"></param>
        /// <param name="keyColumn"></param>
        /// <param name="pivotNameColumn"></param>
        /// <param name="pivotValueColumn"></param>
        /// <returns></returns>
        public static DataTable Pivot2(IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)
        {
            DataTable tmp = new DataTable();
            DataRow r;
            string LastKey = "//dummy//";
            int i, pValIndex, pNameIndex;
            string s;
            bool FirstRow = true;

            // Add non-pivot columns to the data table:

            pValIndex = dataValues.GetOrdinal(pivotValueColumn);
            pNameIndex = dataValues.GetOrdinal(pivotNameColumn);

            for (i = 0; i <= dataValues.FieldCount - 1; i++)
                if (i != pValIndex && i != pNameIndex)
                    tmp.Columns.Add(dataValues.GetName(i), dataValues.GetFieldType(i));

            r = tmp.NewRow();

            // now, fill up the table with the data:
            while (dataValues.Read())
            {
                // see if we need to start a new row
                if (dataValues[keyColumn].ToString() != LastKey)
                {
                    // if this isn't the very first row, we need to add the last one to the table
                    if (!FirstRow)
                        tmp.Rows.Add(r);
                    r = tmp.NewRow();
                    FirstRow = false;
                    // Add all non-pivot columnName values to the new row:
                    for (i = 0; i <= dataValues.FieldCount - 3; i++)
                        r[i] = dataValues[tmp.Columns[i].ColumnName];
                    LastKey = dataValues[keyColumn].ToString();
                }
                // assign the pivot values to the proper columnName; add new columns if needed:
                s = dataValues[pNameIndex].ToString();
                if (!tmp.Columns.Contains(s))
                    tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex));
                r[s] = dataValues[pValIndex];
            }

            // add that final row to the datatable:
            tmp.Rows.Add(r);

            // Close the DataReader
            dataValues.Close();

            // and that's it!
            return tmp;
        }


    }
}